package com.work.dao;

import com.work.pojo.email;
import com.work.pojo.user;
import com.work.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author： Administrator
 * @date： 2021-05-14 14:54
 * @version 1.0
 */
public class userdao {



    public static int insert(user us) throws ClassNotFoundException, SQLException {
              int i=0;
// 1，获取连接.
        try {
            Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
            String sql = "insert into USERMAIL values(SEQ_USERMAIL_ID.nextval,?,?,0,?)";
            //2，2创建prepareStatement对象，执行 sqL语句
            PreparedStatement ps = con.prepareStatement(sql);
            //3.给占位符进行赋值
            ps.setString(1, us.getEmail());
            ps.setString(2, us.getPassword());
            ps.setString(3,us.getCookies());
           i = ps.executeUpdate();
//3.资源释放
            DBUtil.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public static ResultSet selectbyusernameandpassword(user us) throws ClassNotFoundException, SQLException {

        //1，获取连接.
        int i=0;
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from USERMAIL where(EMAIL=? and PASSWORD=?)";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,us.getEmail());
        ps.setString(2,us.getPassword());
        ResultSet rs=ps.executeQuery();
        // 4，处理查询的结果集
       /* while(rs.next()){
            // 获取当前行中的数据
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            int state = rs.getInt("state");
            us = new user(id,username,password,state);
        }*/
        // 5，释放资源，还回连接池中
        DBUtil.close();

        return rs;
    }
    public static ResultSet selectbyusername(user us) throws ClassNotFoundException, SQLException {

        //1，获取连接.
        int i=0;
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from USERMAIL where EMAIL=?";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,us.getEmail());
        ResultSet rs=ps.executeQuery();
        // 4，处理查询的结果集
        // 5，释放资源，还回连接池中
        DBUtil.close();

        return rs;
    }
    //查询
    public static List<user> result() throws ClassNotFoundException, SQLException {
        //0.创建集合
        List<user> user=new ArrayList<>();
        //1，获取连接.
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from USERMAIL";
        PreparedStatement ps = con.prepareStatement(sql1);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            int id=rs.getInt("id");
            String email=rs.getString("email");
            String password=rs.getString("password");
            int state=rs.getInt("state");
            String cookies=rs.getString("cookies");
            //控制台输出
          /*  System.out.println("id："+id+"，username："+username+
                    "，password："+password+"，state："+state);*/
            com.work.pojo.user user1=new user(id,email,password,state,cookies);
            user.add(user1);
        }
        DBUtil.close();
        return user;
    }
    //删除
    public static int delete(user us)throws ClassNotFoundException, SQLException{

        //1，获取连接.
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql2= "delete from USERMAIL where EMAIL=? and PASSWORD=?";
        PreparedStatement ps = con.prepareStatement(sql2);
        ps.setString(1,us.getEmail());
        ps.setString(2,us.getPassword());
        int i = ps.executeUpdate();
        DBUtil.close();
        return i;
    }
    public static int update(user us)throws ClassNotFoundException, SQLException{

        //1，获取连接.
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql3="update USERMAIL set PASSWORD=?,EMAIL=? where ID=?";
        PreparedStatement  ps = con.prepareStatement(sql3);
        ps.setString(1,us.getPassword());
        ps.setString(2,us.getEmail());
        ps.setInt(3,us.getId());
        int i = ps.executeUpdate();
        DBUtil.close();
        return i;

    }

    public static int deletebyid(user user) throws SQLException {

        //1，获取连接.
        Connection con = DBUtil.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql2= "delete from USERMAIL where ID=?";
        PreparedStatement ps = con.prepareStatement(sql2);
        ps.setInt(1, user.getId());
        int i = ps.executeUpdate();
        DBUtil.close();
        return i;
    }
    /**
     * 分页查询语句
     * @param start
     * @param end
     * @return
     * @throws SQLException
     */
    public static List<user> selectForPagination(Integer start, Integer end) throws SQLException {
        List<user> userList = new ArrayList<>();
        Connection con = DBUtil.getConnection();
        String sql = "select * from (" +
                "select rownum as rn,id,email,password,state " +
                "from USERMAIL where rownum<=? order by rownum) " +
                "where rn>=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,end);
        ps.setInt(2,start);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            int id = rs.getInt("id");
            String username = rs.getString("username");
            String password = rs.getString("password");
            int state = rs.getInt("state");
            String actcode=rs.getString("actcode");
            user user = new user(id,username,password,state,actcode);
            userList.add(user);
        }
        DBUtil.close(rs,ps,con);
        return userList;
    }


    /**
     * 查询 user 表中所有的行数
     * @return
     * @throws SQLException
     */
    public static Integer countNum() throws SQLException {
        int count = 0;
        Connection con = DBUtil.getConnection();
        String sql = "select count(*) cn from USERMAIL";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            count = rs.getInt("cn");
        }
        DBUtil.close(rs,ps,con);
        return count;
    }
}

